﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.GERP.GHRMS.DALFactory;
using KPIS.DBM;


namespace KPIS.GERP.GHRMS.BLL
{
    public class Command_BLL
    {

        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        public DataSet ListSalaryCommand()
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_commands as PC, mas_record_statuses RS WHERE PC.RECORD_STATUS=@RecordStatus and PC.cmd_status = RS.RECORD_STATUS ");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }



        public DataSet ListSalaryCommand(string cmd_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_seq", cmd_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_commands as PC, mas_record_statuses RS WHERE PC.RECORD_STATUS=@RecordStatus and PC.cmd_status = RS.RECORD_STATUS and cmd_seq=@cmd_seq ");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public DataSet ListSalaryCommand(int cmd_type_seq ,string blank)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_type_seq", cmd_type_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_commands as PC, mas_record_statuses RS WHERE PC.RECORD_STATUS=@RecordStatus and PC.cmd_status = RS.RECORD_STATUS and cmd_type_seq=@cmd_type_seq "); 
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public DataSet ListSalaryCommandByGroup(int cmd_group_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_group_seq", cmd_group_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_commands as PC, mas_record_statuses RS,hrm_ps_command_types as CT WHERE PC.RECORD_STATUS=@RecordStatus and PC.cmd_status = RS.RECORD_STATUS and cmd_group_seq=@cmd_group_seq and PC.cmd_type_seq = CT.cmd_type_seq ");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }
        public DataSet ListSalaryCommandPromote()
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
           // dbManager.AddParameters(1, "@cmd_group_seq", cmd_group_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_commands as PC, mas_record_statuses RS,hrm_ps_command_types as CT "
                + " WHERE PC.RECORD_STATUS=@RecordStatus "
                + " and PC.cmd_status = RS.RECORD_STATUS "
                + " and PC.cmd_type_seq in ('1001', '1002', '1003', '1004') ");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public void InsertSalaryCommand(string cmd_flag, string cmd_draft_no, string cmd_name, string cmd_ref_no, int cmd_type_seq, int emp_type_seq, string cmd_status, Nullable<int> approved_seq, string approved_name, string approved_positions, Nullable<DateTime> approved_date, string remarks, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@cmd_flag", cmd_flag);
                dbManager.AddParameters(1, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(2, "@cmd_name", cmd_name);
                dbManager.AddParameters(3, "@cmd_ref_no", cmd_ref_no);

                dbManager.AddParameters(4, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(5, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(6, "@cmd_status", cmd_status);
                dbManager.AddParameters(7, "@approved_seq", approved_seq);

                dbManager.AddParameters(8, "@approved_name", approved_name);
                dbManager.AddParameters(9, "@approved_positions", approved_positions);
                dbManager.AddParameters(10, "@approved_date", approved_date);
                dbManager.AddParameters(11, "@remarks", remarks);

                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(14, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_ps_commands (";
                sql += "cmd_flag";
                sql += ",cmd_draft_no";
                sql += ",cmd_name";
                sql += ",cmd_ref_no";
                sql += ",cmd_type_seq";
                sql += ",emp_type_seq";
                sql += ",cmd_status";
                sql += ",approved_seq";
                sql += ",approved_name";
                sql += ",approved_positions";
                sql += ",approved_date";
                sql += ",remarks";
                sql += ",RECORD_STATUS";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@cmd_flag";
                sql += ",@cmd_draft_no";
                sql += ",@cmd_name";
                sql += ",@cmd_ref_no";
                sql += ",@cmd_type_seq";
                sql += ",@emp_type_seq";
                sql += ",@cmd_status";
                sql += ",@approved_seq";
                sql += ",@approved_name";
                sql += ",@approved_positions";
                sql += ",@approved_date";
                sql += ",@remarks";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertSalaryCommand(string cmd_flag, string cmd_draft_no, string cmd_name, string cmd_ref_no, int cmd_type_seq, int emp_type_seq, string cmd_status, Nullable<int> approved_seq, string approved_name, string approved_positions, Nullable<DateTime> approved_date, int confirm, string remarks, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@cmd_flag", cmd_flag);
                dbManager.AddParameters(1, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(2, "@cmd_name", cmd_name);
                dbManager.AddParameters(3, "@cmd_ref_no", cmd_ref_no);

                dbManager.AddParameters(4, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(5, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(6, "@cmd_status", cmd_status);
                dbManager.AddParameters(7, "@approved_seq", approved_seq);

                dbManager.AddParameters(8, "@approved_name", approved_name);
                dbManager.AddParameters(9, "@approved_positions", approved_positions);
                dbManager.AddParameters(10, "@approved_date", approved_date);
                dbManager.AddParameters(11, "@confirm", confirm);
                dbManager.AddParameters(12, "@remarks", remarks);

                dbManager.AddParameters(13, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(15, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_ps_commands (";
                sql += "cmd_flag";
                sql += ",cmd_draft_no";
                sql += ",cmd_name";
                sql += ",cmd_ref_no";
                sql += ",cmd_type_seq";
                sql += ",emp_type_seq";
                sql += ",cmd_status";
                sql += ",approved_seq";
                sql += ",approved_name";
                sql += ",approved_positions";
                sql += ",approved_date";
                sql += ",confirm";
                sql += ",remarks";
                sql += ",RECORD_STATUS";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@cmd_flag";
                sql += ",@cmd_draft_no";
                sql += ",@cmd_name";
                sql += ",@cmd_ref_no";
                sql += ",@cmd_type_seq";
                sql += ",@emp_type_seq";
                sql += ",@cmd_status";
                sql += ",@approved_seq";
                sql += ",@approved_name";
                sql += ",@approved_positions";
                sql += ",@approved_date";
                sql += ",@confirm";
                sql += ",@remarks";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void UpdateSalaryCommand(int Sequence, string cmd_flag, string cmd_draft_no, string cmd_name, string cmd_ref_no, int cmd_type_seq, int emp_type_seq, string cmd_status, Nullable<int> approved_seq, string approved_name, string approved_positions, Nullable<DateTime> approved_date, string remarks, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@cmd_flag", cmd_flag);
                dbManager.AddParameters(2, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(3, "@cmd_name", cmd_name);
                dbManager.AddParameters(4, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(5, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(6, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(7, "@cmd_status", cmd_status);
                dbManager.AddParameters(8, "@approved_seq", approved_seq);
                dbManager.AddParameters(9, "@approved_name", approved_name);
                dbManager.AddParameters(10, "@approved_positions", approved_positions);
                dbManager.AddParameters(11, "@approved_date", approved_date);
                dbManager.AddParameters(12, "@remarks", remarks);
                dbManager.AddParameters(13, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@updated_by", UpdateBy);
                dbManager.AddParameters(15, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_ps_commands SET cmd_flag=@cmd_flag, cmd_draft_no=@cmd_draft_no,cmd_name=@cmd_name,cmd_ref_no=@cmd_ref_no,cmd_type_seq=@cmd_type_seq,emp_type_seq=@emp_type_seq,cmd_status=@cmd_status,approved_seq=@approved_seq,approved_name=@approved_name,approved_positions=@approved_positions,approved_date=@approved_date,remarks=@remarks,record_status=@RecordStatus,updated_by=@updated_by,updated_when=@updated_when WHERE cmd_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateSalaryCommand(int Sequence, string cmd_flag, string cmd_draft_no, string cmd_name, string cmd_ref_no, int cmd_type_seq, int emp_type_seq, string cmd_status, Nullable<int> approved_seq, string approved_name, string approved_positions, Nullable<DateTime> approved_date, int confirm, string remarks, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(17);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@cmd_flag", cmd_flag);
                dbManager.AddParameters(2, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(3, "@cmd_name", cmd_name);
                dbManager.AddParameters(4, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(5, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(6, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(7, "@cmd_status", cmd_status);
                dbManager.AddParameters(8, "@approved_seq", approved_seq);
                dbManager.AddParameters(9, "@approved_name", approved_name);
                dbManager.AddParameters(10, "@approved_positions", approved_positions);
                dbManager.AddParameters(11, "@approved_date", approved_date);
                dbManager.AddParameters(12, "@confirm", confirm);
                dbManager.AddParameters(13, "@remarks", remarks);
                dbManager.AddParameters(14, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(15, "@updated_by", UpdateBy);
                dbManager.AddParameters(16, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_ps_commands SET cmd_flag=@cmd_flag, cmd_draft_no=@cmd_draft_no,cmd_name=@cmd_name,cmd_ref_no=@cmd_ref_no,cmd_type_seq=@cmd_type_seq,emp_type_seq=@emp_type_seq,cmd_status=@cmd_status,approved_seq=@approved_seq,approved_name=@approved_name,approved_positions=@approved_positions,approved_date=@approved_date,confirm=@confirm,remarks=@remarks,record_status=@RecordStatus,updated_by=@updated_by,updated_when=@updated_when WHERE cmd_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }



        public void DeleteSalaryCommand(int Sequence, string UpdateBy)
        { 
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_ps_commands SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE cmd_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        

        //=====================================================

        public DataSet ListSalaryCommandDetail(int cmd_dtls_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_dtls_seq", cmd_dtls_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select *, PCD.remarks as pcd_remarks from hrm_ps_command_dtls as PCD, hrm_ps_commands as PC, hrm_ps_employees as PE, hrm_mp_classes as MC WHERE PCD.RECORD_STATUS=@RecordStatus and PCD.cmd_seq = PC.cmd_seq and PCD.emp_seq = PE.emp_seq and PCD.new_class_seq = MC.class_seq and PCD.cmd_dtls_seq = @cmd_dtls_seq");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }





        public DataSet ListSalaryCommandDetail(string cmd_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_seq", cmd_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select *, CONCAT( prefix_name, first_name,' ',last_name ) full_name,PCD.remarks as pcd_remarks from hrm_ps_command_dtls as PCD, hrm_ps_commands as PC, hrm_ps_employees as PE, hrm_mp_classes as MC,mas_name_prefixes as NP WHERE PCD.RECORD_STATUS=@RecordStatus and PCD.cmd_seq = PC.cmd_seq and PCD.emp_seq = PE.emp_seq and PCD.new_class_seq = MC.class_seq and PE.name_prefix_seq = NP.name_prefix_seq and PCD.cmd_seq = @cmd_seq");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public DataSet ListSalaryCommandDetail(string cmd_seq, string emp_seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_command_dtls as PCD, hrm_ps_commands as PC, hrm_ps_employees as PE, hrm_mp_classes as MC WHERE PCD.RECORD_STATUS=@RecordStatus and PCD.cmd_seq = PC.cmd_seq and PCD.emp_seq = PE.emp_seq and PCD.new_class_seq = MC.class_seq and PCD.cmd_seq = " + cmd_seq + " and PCD.emp_seq in (" + emp_seq + ")");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }


        public bool ChkDuplicate(int emp_seq, string cmd_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_command_dtls where emp_seq = " + emp_seq + " and cmd_seq = " + cmd_seq + " and record_status = '" + SystemInfo._ActiveRecordStatus + "'");
            if (ds.Tables[0].Rows.Count != 0)
                return true;
            else
                return false;
        }


        public void InsertSalaryCommandDetail(int cmd_seq, int emp_seq, DateTime start_date, int new_pos_seq, string new_pos_ref, string new_pos_name, int new_class_seq, int new_job_site_seq, string new_job_site_name, Double new_salary, Double old_salary, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(14);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@start_date", start_date);
                dbManager.AddParameters(3, "@new_pos_seq", new_pos_seq);

                dbManager.AddParameters(4, "@new_pos_ref", new_pos_ref);
                dbManager.AddParameters(5, "@new_pos_name", new_pos_name);
                dbManager.AddParameters(6, "@new_class_seq", new_class_seq);
                dbManager.AddParameters(7, "@new_job_site_seq", new_job_site_seq);

                dbManager.AddParameters(8, "@new_job_site_name", new_job_site_name);
                dbManager.AddParameters(9, "@new_salary", new_salary);
                dbManager.AddParameters(10, "@old_salary", old_salary);

                dbManager.AddParameters(11, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(12, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(13, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_ps_command_dtls (";
                sql += "cmd_seq";
                sql += ",emp_seq";
                sql += ",start_date";
                sql += ",new_pos_seq";
                sql += ",new_pos_ref";
                sql += ",new_pos_name";
                sql += ",new_class_seq";
                sql += ",new_job_site_seq";
                sql += ",new_job_site_name";
                sql += ",new_salary";
                sql += ",old_salary";
                sql += ",RECORD_STATUS";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@cmd_seq";
                sql += ",@emp_seq";
                sql += ",@start_date";
                sql += ",@new_pos_seq";
                sql += ",@new_pos_ref";
                sql += ",@new_pos_name";
                sql += ",@new_class_seq";
                sql += ",@new_job_site_seq";
                sql += ",@new_job_site_name";
                sql += ",@new_salary";
                sql += ",@old_salary";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertSalaryCommandDetail(int cmd_seq, int emp_seq, Nullable<DateTime> start_date, Nullable<int> new_pos_seq, string new_pos_ref, string new_pos_name, Nullable<int> new_class_seq, Nullable<int> new_job_site_seq, string new_job_site_name, Nullable<Double> new_salary, Nullable<Double> old_salary, Nullable<Double> prm_base, Nullable<Double> prm_percent, Nullable<Double> prm_amount, string remarks, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(18);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@start_date", start_date);
                dbManager.AddParameters(3, "@new_pos_seq", new_pos_seq);

                dbManager.AddParameters(4, "@new_pos_ref", new_pos_ref);
                dbManager.AddParameters(5, "@new_pos_name", new_pos_name);
                dbManager.AddParameters(6, "@new_class_seq", new_class_seq);
                dbManager.AddParameters(7, "@new_job_site_seq", new_job_site_seq);

                dbManager.AddParameters(8, "@new_job_site_name", new_job_site_name);
                dbManager.AddParameters(9, "@new_salary", old_salary + prm_amount);
                dbManager.AddParameters(10, "@old_salary", old_salary);

                dbManager.AddParameters(11, "@prm_base", prm_base);
                dbManager.AddParameters(12, "@prm_percent", prm_percent);
                dbManager.AddParameters(13, "@prm_amount", prm_amount);
                dbManager.AddParameters(14, "@remarks", remarks);
            
                dbManager.AddParameters(15, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(16, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(17, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_ps_command_dtls (";
                sql += "cmd_seq";
                sql += ",emp_seq";
                sql += ",start_date";
                sql += ",new_pos_seq";
                sql += ",new_pos_ref";
                sql += ",new_pos_name";
                sql += ",new_class_seq";
                sql += ",new_job_site_seq";
                sql += ",new_job_site_name";
                sql += ",new_salary";
                sql += ",old_salary";

                sql += ",prm_base";
                sql += ",prm_percent";
                sql += ",prm_amount";
                sql += ",remarks";

                sql += ",RECORD_STATUS";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@cmd_seq";
                sql += ",@emp_seq";
                sql += ",@start_date";
                sql += ",@new_pos_seq";
                sql += ",@new_pos_ref";
                sql += ",@new_pos_name";
                sql += ",@new_class_seq";
                sql += ",@new_job_site_seq";
                sql += ",@new_job_site_name";
                sql += ",@new_salary";
                sql += ",@old_salary";

                sql += ",@prm_base";
                sql += ",@prm_percent";
                sql += ",@prm_amount";
                sql += ",@remarks";

                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void UpdateSalaryCommandDetail(int Sequence, DateTime start_date, Double new_salary, Double old_salary, Nullable<Double> prm_base, Nullable<Double> prm_percent, Nullable<Double> prm_amount,string remarks, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(11);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@start_date", start_date);
                dbManager.AddParameters(2, "@new_salary", new_salary);
                dbManager.AddParameters(3, "@old_salary", old_salary);

                dbManager.AddParameters(4, "@prm_base", prm_base);
                dbManager.AddParameters(5, "@prm_percent", prm_percent);
                dbManager.AddParameters(6, "@prm_amount", prm_amount);
                dbManager.AddParameters(7, "@remarks", remarks);

                dbManager.AddParameters(8, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(9, "@updated_by", UpdateBy);
                dbManager.AddParameters(10, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_ps_command_dtls SET start_date=@start_date, new_salary=@new_salary, old_salary=@old_salary, prm_base=@prm_base, prm_percent=@prm_percent, prm_amount=@prm_amount, remarks=@remarks, record_status=@RecordStatus,updated_by=@updated_by,updated_when=@updated_when WHERE cmd_dtls_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }



        public void UpdateSalaryCommandDetail(int Sequence, DateTime start_date, Double new_salary, Double old_salary, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@start_date", start_date);
                dbManager.AddParameters(2, "@new_salary", new_salary);
                dbManager.AddParameters(3, "@old_salary", old_salary);
                dbManager.AddParameters(4, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@updated_by", UpdateBy);
                dbManager.AddParameters(6, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_ps_command_dtls SET start_date=@start_date, new_salary=@new_salary, old_salary=@old_salary, record_status=@RecordStatus,updated_by=@updated_by,updated_when=@updated_when WHERE cmd_dtls_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public DataSet ListBonusCommandDetail(string cmd_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_seq", cmd_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select *, CONCAT( prefix_name, first_name,' ',last_name ) full_name,PRD.remarks as pcd_remarks "
                   + " from hrm_pr_command_dtls as PRD, hrm_ps_commands as PC, hrm_ps_employees as PE, hrm_mp_classes as MC,mas_name_prefixes as NP "
                   + " WHERE PRD.RECORD_STATUS=@RecordStatus and PRD.cmd_seq = PC.cmd_seq and PRD.emp_seq = PE.emp_seq and PRD.class_seq = MC.class_seq and PE.name_prefix_seq = NP.name_prefix_seq and PRD.cmd_seq = @cmd_seq";

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public void InsertBonusCommandDetail(string cmd_seq, string ext_seq, string emp_seq, string pos_seq, string pos_ref_no, string pos_name, string class_seq, string job_site_seq, string job_site_name, string salary, string ext_amount, string remarks, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@ext_seq", ext_seq);
                dbManager.AddParameters(2, "@emp_seq", emp_seq);
                dbManager.AddParameters(3, "@pos_seq", pos_seq);

                dbManager.AddParameters(4, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(5, "@pos_name", pos_name);
                dbManager.AddParameters(6, "@class_seq", class_seq);
                dbManager.AddParameters(7, "@job_site_seq", job_site_seq);

                dbManager.AddParameters(8, "@job_site_name", job_site_name);
                dbManager.AddParameters(9, "@salary", salary);
                dbManager.AddParameters(10, "@ext_amount", ext_amount);
                dbManager.AddParameters(11, "@remarks", remarks);

                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(14, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_pr_command_dtls (";
                sql += "cmd_seq";
                sql += ",ext_seq";
                sql += ",emp_seq";
                sql += ",pos_seq";
                sql += ",pos_ref_no";
                sql += ",pos_name";
                sql += ",class_seq";
                sql += ",job_site_seq";
                sql += ",job_site_name";
                sql += ",salary";
                sql += ",ext_amount";
                sql += ",remarks";
                sql += ",RECORD_STATUS";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";

                sql += "@cmd_seq";
                sql += ",@ext_seq";
                sql += ",@emp_seq";
                sql += ",@pos_seq";
                sql += ",@pos_ref_no";
                sql += ",@pos_name";
                sql += ",@class_seq";
                sql += ",@job_site_seq";
                sql += ",@job_site_name";
                sql += ",@salary";
                sql += ",@ext_amount";
                sql += ",@remarks";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void UpdateBonusCommandDetail(int Sequence, Double ext_amount, string remarks, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@ext_amount", ext_amount);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@updated_by", UpdateBy);
                dbManager.AddParameters(5, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_pr_command_dtls SET ext_amount=@ext_amount, remarks=@remarks, record_status=@RecordStatus,updated_by=@updated_by,updated_when=@updated_when WHERE cmd_ext_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void DeleteBonusCommandDetail(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_pr_command_dtls SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE cmd_ext_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

    }
}
